<?php

include_once("config/config.inc.php");

class monthly
{
	public function monthly($siteid,$startdt,$enddt){
		$this->siteid=$siteid;
		$this->startdt=$startdt;
		$this->enddt=$enddt;
		//$this->totalsales= getTotalSales($this->siteid,$this->startdt,$this->enddt);
		$this->numguest = $this->getNumGuest($this->siteid,$this->startdt,$this->enddt);
		$this->roomsales = $this->getRoomSales($this->siteid,$this->startdt,$this->enddt);

		$this->bevsales = $this->getBeverageSales($this->siteid,$this->startdt,$this->enddt);
		$this->foodonlysales = $this->getFoodSales($this->siteid,$this->startdt,$this->enddt)-$this->bevsales;
		$this->foodsales = $this->bevsales + $this->foodonlysales;
		$this->beersales = $this->getBeerSales($this->siteid,$this->startdt,$this->enddt);
		$this->miscsales = $this->getMiscSales($this->siteid,$this->startdt,$this->enddt);	
		$this->overtimesales = $this->getOvertimeSales($this->siteid,$this->startdt,$this->enddt);
		$this->totalsales = $this->roomsales + $this->foodsales + $this->beersales + $this->miscsales + $this->overtimesales;

		
	}	

	public function getNumGuest($siteid,$startdt,$enddt)
	{
		$sql = "select count(a.occupancy_id) from occupancy a, rooms b
				where b.room_id = a.room_id
				and a.actual_checkout >= '$startdt' 
				and a.actual_checkout <= '$enddt' ";
				
		if($siteid){
				$sql .= " and b.site_id = '$siteid'";
		}
		
		$res = mysql_query($sql) or die(mysql_error().$sql);
		list($cnt)=mysql_fetch_row($res);

		return $cnt;
	}

	public function getRoomSales($siteid,$startdt,$enddt){	
		$sql = "select unit_cost*qty from room_sales a, occupancy b, rooms c
			where a.status in ('Paid')
			and a.item_id = '15'		
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
		
		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}
			
		$res = mysql_query($sql);
		
		while(list($roomamount)=mysql_fetch_row($res))
		{
			$roomtot +=  $roomamount;
		}


		$sql = "select unit_cost*qty from occupancy a, room_sales b,  rooms c
				where b.status in ('Paid')
				and a.occupancy_id = b.occupancy_id	
				and a.room_id = c.room_id	
				and a.actual_checkout >= '$startdt' 
				and a.actual_checkout <= '$enddt'
				and b.item_id = '17' ";

		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}
		
		
		$res = mysql_query($sql);
		while(list($disc)=mysql_fetch_row($res))
		{
			$disctotal += $disc;
		}
		$roomtot = $roomtot - abs($disctotal);
		return $roomtot;
	}
	public function getFoodSales($siteid,$startdt,$enddt){
		$sql = "select unit_cost*qty from fnb_sales a,  occupancy b, rooms c
				where a.status in ('Paid')
				and a.category_id <> '21'
				and a.category_id <> '17'			
				and b.actual_checkout >= '$startdt' 
				and b.actual_checkout <= '$enddt' 
				and b.occupancy_id = a.occupancy_id
				and c.room_id = b.room_id ";
		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}

				
		$res = mysql_query($sql) or die(mysql_error());
		
		while(list($fnbamount)=mysql_fetch_row($res))
		{
			$fnbtot += $fnbamount;
		}
		$this->kitchen += $fnbtot;
		return $fnbtot;
	}
	public function getBeverageSales($siteid,$startdt,$enddt){
		$sql = "select unit_cost*qty from fnb_sales a,  occupancy b, rooms c
				where a.status in ('Paid')
				and a.category_id = '16'							
				and b.actual_checkout >= '$startdt' 
				and b.actual_checkout <= '$enddt' 
				and b.occupancy_id = a.occupancy_id
				and c.room_id = b.room_id ";
		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}

				
		$res = mysql_query($sql) or die(mysql_error());
		
		while(list($bevamount)=mysql_fetch_row($res))
		{
			$bevtot += $bevamount;
		}		
		return $bevtot;
	}
	public function getBeerSales($siteid,$startdt,$enddt){
		$sql = "select unit_cost*qty from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and (a.category_id = '21'
			or a.category_id = '17')
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}
			
		$res = mysql_query($sql);
		
		while(list($beeramount)=mysql_fetch_row($res))
		{
			$beertot += $beeramount;
		}
		$this->kitchen += $beertot;
		return $beertot;
	}
	public function getMiscSales($siteid,$startdt,$enddt){
		$sql = "select unit_cost*qty from room_sales a, occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '3'		
			and b.actual_checkout >= '$startdt' 
			and b.actual_checkout <= '$enddt'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
			
		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}
		$res = mysql_query($sql);
		
		while(list($miscamount)=mysql_fetch_row($res))
		{
			$misctot +=  $miscamount;
		}
		return $misctot;
	}
	public function getOvertimeSales($siteid,$startdt,$enddt){
		$sql = "select unit_cost*qty
			from occupancy a, room_sales b, rooms c
			where b.item_id = '16'
			and a.occupancy_id = b.occupancy_id
			and c.room_id = a.room_id
			and a.actual_checkout >= '$startdt' 
			and a.actual_checkout <= '$enddt' ";
			
		if($siteid){
			$sql .= " and c.site_id = '$siteid'";
		}	
		$res = mysql_query($sql) or die(mysql_error().$sql);

		while(list($ecost)=mysql_fetch_row($res))
		{
			$out_ot1 += $ecost;
		}

		return $out_ot1;
	}
	public function getCoopSales()
	{
		/*
		$sql = "select unit_cost*qty from room_sales a, occupancy b, rooms c
			where a.status in ('Paid')
			and a.sales_date >= '".$this->startdt."' 
			and a.sales_date <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id 
			and a.category_id = '2' ";
		if($this->siteid){
			$sql .= " and c.site_id = '$siteid'";
		}
		$res = mysql_query($sql);
		while(list($coopamount)=mysql_fetch_row($res))
		{
			$cooptot +=  $coopamount;
		}
		*/

		$sql = "select sum(unit_cost*qty) from room_sales a, occupancy b, rooms c
			where a.status in ('Paid')
			and a.sales_date >= '".$this->startdt."' 
			and a.sales_date <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id 
			and a.category_id = '2' ";
		if($this->siteid){
			$sql .= " and c.site_id = '$siteid'";
		}
		$res = mysql_query($sql);
		list($cooptot)=mysql_fetch_row($res);
		return $cooptot;
	}
	public function getFOG()
	{
		$sql = "select count(fnbsales_id) from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and b.actual_checkout >= '".$this->startdt."' 
			and b.actual_checkout <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."' ";
		}
		$sql .= " group by order_code";
		$res = mysql_query($sql);
		$num = mysql_num_rows($res);
		return $num;
	}
	public function getFonlyOG()
	{
		$sql = "select count(fnbsales_id) from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and a.category_id <> '16'
			and b.actual_checkout >= '".$this->startdt."' 
			and b.actual_checkout <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."' ";
		}
		$sql .= " group by order_code";
		$res = mysql_query($sql);
		$num = mysql_num_rows($res);
		return $num;
	}
	public function getBOG()
	{
		$sql = "select unit_cost*qty from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and (a.category_id = '21'
			or a.category_id = '17')
			and b.actual_checkout >= '".$this->startdt."' 
			and b.actual_checkout <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."' ";
		}
		$sql .= " group by order_code";		
		
		$res = mysql_query($sql);
		$num = mysql_num_rows($res);

		return $num;
	}
	public function getBBOG()
	{
		$sql = "select unit_cost*qty from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id = '16' 
			and b.actual_checkout >= '".$this->startdt."' 
			and b.actual_checkout <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id ";
		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."' ";
		}
		$sql .= " group by order_code";		
		$res = mysql_query($sql);
		$num = mysql_num_rows($res);

		return $num;
	}
	public function getNumFnbBestSeller($category_id)
	{
		$sql = "select count(fnbsales_id) from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and a.category_id <> '21'
			and a.category_id <> '17'			
			and b.actual_checkout >= '".$this->startdt."' 
			and b.actual_checkout <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id";
			
		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."' ";
		}
		$sql .= " group by a.category_id order by count(fnbsales_id)";	
		
		$res = mysql_query($sql);
		list($cnt)=mysql_fetch_row($res);
		return $cnt;
	}
	public function getMiniRefSales()
	{
		$sql = "select unit_cost*qty from fnb_sales a,  occupancy b, rooms c
			where a.status in ('Paid')
			and b.actual_checkout >= '".$this->startdt."' 
			and b.actual_checkout <= '".$this->enddt."'
			and b.occupancy_id = a.occupancy_id
			and c.room_id = b.room_id 
			and a.order_code like '%17132' ";
		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."'";
		}
			
		$res = mysql_query($sql);
		
		
		while(list($minirefamount)=mysql_fetch_row($res))
		{
			$minireftot += $minirefamount;
		}
		
		return $minireftot;
	}

	public function getSalesByCC($cc)
	{
		
		$sql = "select a.amount from salesreceipts  a, occupancy b, card_payment_details c, rooms d
		where a.tendertype = 'Card' 
		and b.actual_checkout >= '".$this->startdt."' 
		and b.actual_checkout <= '".$this->enddt."'
		and b.occupancy_id = a.occupancy_id
		and b.room_id = d.room_id
		and a.salesreceipt_id = c.salesreceipt_id ";

		if($cc)
		{
			$sql .= " and c.card_type = '".trim($cc)."' ";
		}
		if($this->siteid){
			$sql .= " and d.site_id = '".$this->siteid."'";
		}
			
		$res = mysql_query($sql);
		
		while(list($ccamount)=mysql_fetch_row($res))
		{
			$cctot += $ccamount;
		}
		
		return $cctot;
		
	}
	public function getDiscountAmount($percent)
	{
		$sql = "select a.unit_cost*a.qty,a.occupancy_id from room_sales a, occupancy b, rooms c
		where b.occupancy_id = a.occupancy_id
		and a.item_id = '17'
		and a.status in ('Paid')
		and b.room_id = c.room_id
		and b.actual_checkout >= '".$this->startdt."' 
		and b.actual_checkout <= '".$this->enddt."'";
		


		if($this->siteid){
			$sql .= " and c.site_id = '".$this->siteid."'";
		}
			
		$res = mysql_query($sql);
		
		$num = mysql_num_rows($res);
		
		while(list($discamount,$occupancy_id)=mysql_fetch_row($res))
		{
			$discamount = abs($discamount);
			if($percent)
			{
				$_sql = "select unit_cost*qty from room_sales where item_id = '15' and status in ('Paid') and occupancy_id = '$occupancy_id'";
				$_res = mysql_query($_sql);
				list($amount)=mysql_fetch_row($_res);
				$perc = ($discamount/$amount)*100;
				if($perc == $percent)
				{
					$disctot += $discamount;
				}
				else if($perc != "100" && $perc != "50" && $perc != "30" && $perc != "20" && $perc != "10")
				{
					$val += $discamount;
				}				
			}		
			else
			{
				$disctot += $discamount;				
			}

			
			if($percent=="others")
			{
				$disctot = $val;
			}
			
		}
		
		return $disctot;
	}

	
}
?>